#! /bin/bash
HIVE_HOME=/usr/bin/hive
${HIVE_HOME} -S -e "
SET hive.auto.convert.join=false;
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.exec.max.dynamic.partitions.pernode=10000;
SET hive.exec.max.dynamic.partitions=10000;
SET hive.exec.max.created.files=150000;
INSERT overwirte zz15_dm_renxinlei.class_attendance_byyear PARTITION(yearinfo,monthinfo,dayinfo)
SELECT
    yearinfo AS dateinfo,
    class_id,
    sum(studying_student_count) AS studying_student_count, 
    --每月每班，上午、下午、晚自习出勤人数/出勤率
    sum( morning_att_count) AS morning_att_count,
    concat(round(sum( morning_att_count) / sum(studying_student_count ) * 100,2),'%') AS morning_att_ratio, 
    sum( afternoon_att_count) AS afternoon_att_count,
    concat(round(sum( afternoon_att_count) / sum(studying_student_count ) * 100,2),'%') AS afternoon_att_ratio, 
    sum( evening_att_count) AS evening_att_count,
    concat(round(sum( evening_att_count) / sum(studying_student_count ) * 100,2),'%') AS evening_att_ratio,
    --每月每班，上午、下午、晚自习迟到人数/迟到率
    sum( morning_late_count) AS morning_late_count,
    concat(round(sum( morning_late_count) / sum(studying_student_count ) * 100,2),'%') AS morning_late_ratio, 
    sum( afternoon_late_count) AS afternoon_late_count,
    concat(round(sum( afternoon_late_count) / sum(studying_student_count ) * 100,2),'%') AS afternoon_late_ratio, 
    sum( evening_late_count) AS evening_late_count,
    concat(round(sum( evening_late_count) / sum(studying_student_count ) * 100,2),'%') AS evening_late_ratio,
    --每月每班，上午、下午、晚自习请假人数/请假率
    sum( morning_leave_count) AS morning_leave_count,
    concat(round(sum( morning_leave_count) / sum(studying_student_count ) * 100,2),'%') AS morning_leave_ratio, 
    sum( afternoon_leave_count) AS afternoon_leave_count,
    concat(round(sum( afternoon_leave_count) / sum(studying_student_count ) * 100,2),'%') AS afternoon_leave_ratio, 
    sum( evening_leave_count) evening_leave_count,
    concat(round(sum( evening_leave_count) / sum(studying_student_count ) * 100,2),'%') AS evening_leave_ratio,
    --每月每班，上午、下午、晚自习旷课人数/旷课率
    sum( morning_truant_count) AS morning_truant_count,
    concat(round(sum( morning_truant_count) / sum(studying_student_count ) * 100,2),'%') AS morning_truant_ratio, 
    sum( afternoon_truant_count) AS afternoon_truant_count,
    concat(round(sum( afternoon_truant_count) / sum(studying_student_count ) * 100,2),'%') AS afternoon_truant_ratio, 
    sum( evening_truant_count) AS evening_truant_count,
    concat(round(sum( evening_truant_count) / sum(studying_student_count ) * 100,2),'%') AS evening_truant_ratio,
    '5' AS time_type,
    yearinfo,
    '-1' AS monthinfo,
    '-1' AS dayinfo
FROM zz15_dws_renxinlei.attendance_summary
GROUP BY yearinfo,class_id